
--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_fab_lance AS (SELECT * FROM &erp.fab_lance WHERE 1=2);

/*SELECT e.ID AS lance_cod,
       e.LIBELLE,
       e.typlanc,
       e.MAG depot,
       To_Date('31/12/1967','dd/mm/yyyy')+e.FINFAB_MOL date_fin,
       datclot closoupas,
       e.SFA unfab
FROM TERP_MLI2.uv_pencours e
ORDER BY DATE_FIN  desc  */



DELETE FROM se_fab_lance;

 INSERT INTO se_fab_lance
 (SELECT e.ID lance_cod,
         0 enreg_id,
         Decode(e.typlanc,'PLA','PDPMO','APP','FAB',e.typlanc) typlanc_cod,
         NULL affaire_cod,
         NULL prfchif_cod,
         e.MAG depot_cod,
         e.SFA unfab_cod,
         NULL prfmemo_cod,
         Decode(e.typlanc,'PLA','PDPMO','APP','FAB',e.typlanc) domfab_cod,
         NULL famlanc_cod,
         e.LIBELLE lance_lib,
         NULL lance_lib2,
         'N' enreg_s,
         Nvl2(datclot,'C','R') statut_typ,
         NULL debplustot_dat,
         To_Date('31/12/1967','dd/mm/yyyy')+e.FINFAB_MOL finplustard_dat,
         NULL finimpose_dat
    FROM uv_pencours e
    WHERE  e.NUMPDP IS null
) ;

 UPDATE se_fab_lance tf SET enreg_id=(select enreg_id FROM &erp.fab_lance f WHERE f.lance_cod=tf.lance_cod) ;
 UPDATE se_fab_lance tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;


 CALL uvse_synchroverserp('lance_cod','FAB_LANCE','O') ;

 commit;


select * from (
SELECT &outils.stragg(source) source,lance_cod, enreg_id, typlanc_cod, affaire_cod, prfchif_cod, depot_cod, unfab_cod, prfmemo_cod, domfab_cod, famlanc_cod, lance_lib, lance_lib2, enreg_s, statut_typ, debplustot_dat, finplustard_dat, finimpose_dat
FROM (
  SELECT 'uv' source ,g.* FROM se_fab_lance g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.fab_lance g
  )
GROUP BY lance_cod, enreg_id, typlanc_cod, affaire_cod, prfchif_cod, depot_cod, unfab_cod, prfmemo_cod, domfab_cod, famlanc_cod, lance_lib, lance_lib2, enreg_s, statut_typ, debplustot_dat, finplustard_dat, finimpose_dat
HAVING Count(1)!=2) r
ORDER BY lance_cod;